package com.alnex.poi.demo;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import com.alnex.poi.bean.TestData;
import com.alnex.poi.utils.ExcelImportUtil;

public class POITest {

    @Test
    public void t1() throws IOException {
        //1 创建工作簿
        Workbook wk = new XSSFWorkbook();
        //2 创建sheet表单
        Sheet sheet = wk.createSheet("POI测试表单");
        Row row = sheet.createRow(0);
        Cell cell0 = row.createCell(0);
        cell0.setCellValue("序列号");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("姓名");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("年龄");
        //3 文件流
        FileOutputStream fos = new FileOutputStream("E:\\poi\\in\\测试.xlsx");
        //写入
        wk.write(fos);
        wk.close();
    }

    //样式设置
    @Test
    public void t2() throws IOException {
        //1 创建工作簿
        Workbook wk = new XSSFWorkbook();
        //2 创建sheet表单
        Sheet sheet = wk.createSheet("POI测试表单");
        CellStyle style = wk.createCellStyle();
        //边框
        style.setBorderBottom(BorderStyle.DASH_DOT);
        style.setBorderBottom(BorderStyle.HAIR);
        //字体
        Font font = wk.createFont();
        font.setFontName("小四");//字体
        font.setFontHeightInPoints((short) 28);//字号
        style.setFont(font);

        Row row = sheet.createRow(0);
        //宽
        sheet.setColumnWidth(0, 31 * 256);
        //高
        row.setHeightInPoints(50);

        //居中显示
        style.setAlignment(HorizontalAlignment.CENTER);//水平
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直

        //样式
        row.setRowStyle(style);

        Cell cell0 = row.createCell(0);
        cell0.setCellValue("序列号");
        Cell cell1 = row.createCell(1);
        cell1.setCellValue("姓名");
        Cell cell2 = row.createCell(2);
        cell2.setCellValue("年龄");

        //合并单元格
        CellRangeAddress cellAddresses = new CellRangeAddress(1, 2, 0, 2);
        sheet.addMergedRegion(cellAddresses);

        //3 文件流
        FileOutputStream fos = new FileOutputStream("E:\\poi\\样式设置测试.xlsx");
        //写入
        wk.write(fos);
        wk.close();
    }

    @Test
    public void t3() throws IOException {
        //1 创建工作簿
        Workbook wk = new XSSFWorkbook();
        //2 创建sheet表单
        Sheet sheet = wk.createSheet("绘图测试表单");
        //3 获取图片信息
        FileInputStream inputStream = new FileInputStream("D:\\10157\\桌面\\app.jpg");
        byte[] bytes = IOUtils.toByteArray(inputStream);
        //读取图片到二进制数组
        inputStream.read(bytes);
        //向Exce1添加一张图片,并返回该图片在Exce1中的图片集合中的下标
        int pictureIndex = wk.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //绘图工具类
        CreationHelper helper = wk.getCreationHelper();
        //创建绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        //创建描点 设置坐标
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(2);
        anchor.setRow1(2);
        //创建图片
        Picture picture = patriarch.createPicture(anchor, pictureIndex);
        picture.resize();
        //输出流
        FileOutputStream fos = new FileOutputStream("E:\\poi\\app.xlsx");
        wk.write(fos);

        inputStream.close();
        fos.close();
    }

    //加载excel
    @Test
    public void t4() throws IOException {
        //1 创建工作簿
        Workbook wk = new XSSFWorkbook("E:\\poi\\hr-demo.xlsx");
        //获取sheet
        Sheet sheet = wk.getSheetAt(0);

        int lastRowNum = sheet.getLastRowNum();

        // System.out.println(lastRowNum);//0

        Row row = null;
        Cell cell = null;

        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            row = sheet.getRow(rowNum);
            // System.out.println(row.getLastCellNum());//3

            StringBuilder sb = new StringBuilder();
            //每行所有单元格
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                cell = row.getCell(cellNum);
                sb.append(getValues(cell)).append("-");
            }
            System.out.println(sb.toString());
        }

    }

    //获取数据
    private static Object getValues(Cell cell) {
        Object value = null;
        switch (cell.getCellType()) {
            case STRING://字符串类型
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            default:
                break;
        }
        return value;
    }

    //导入数据
    @Test
    public void t5() throws FileNotFoundException {

        FileInputStream is = new FileInputStream("E:\\poi\\hr-demo.xlsx");
        List list = new ExcelImportUtil(Object.class).readExcel(is, 0, 0);

        for (Object o : list) {
            System.out.println(o.toString());
        }
    }

    //百万数据读取
    @Test
    public void t6() throws FileNotFoundException {
        FileInputStream is = new FileInputStream("E:\\poi\\in\\人员信息.xlsx");
        List list = new ExcelImportUtil(TestData.class).readExcel(is, 1, 0);
        System.out.println(list.size());
    }
}
